package com.ruoyi.odorservice.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.ruoyi.common.core.domain.Result;
import com.ruoyi.odorservice.entity.Sample;
import com.ruoyi.odorservice.entity.SampleDetail;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

public class ExcelUtils {
    /**
     * 默认sheet名称
     */
    private static final String DEFAULT_SHEET_NAME = "sheet";
    /**
     * 单个Sheet页最大行数（除去标题）
     */
    private static final int SINGLE_SHEET_MAX_ROWS = 65535;
    /**
     * 默认单元格宽度
     */
    private static final int DEFAULT_CELL_WIDTH = 5000;
    /**
     * 默认日期格式
     */
    private static final String DEFAULT_DATE_PATTERN = "yyyy-MM-dd HH:mm:ss";

    public static void read(MultipartFile file){
        try {
            read(file.getInputStream());
        } catch (IOException e) {
            throw new RuntimeException("Excel读取异常！");
        }
    }
    public static void read(InputStream inputStream){
        try (Workbook workbook = new XSSFWorkbook(inputStream)) {  // 使用 XSSFWorkbook 适用于 .xlsx 文件
            // 获取所有Sheet
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                Iterator<Row> rowIterator = sheet.iterator();
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        switch (cell.getCellType()) {
                            case STRING:
                                System.out.print(cell.getStringCellValue() + "\t");
                                break;
                            case NUMERIC:
                                System.out.print(cell.getNumericCellValue() + "\t");
                                break;
                            case BOOLEAN:
                                System.out.print(cell.getBooleanCellValue() + "\t");
                                break;
                            default:
                                System.out.print("\t");
                        }
                    }
                    System.out.println();
                }
            }
            workbook.close();
            inputStream.close();
        } catch (IOException e) {
            throw new RuntimeException("Excel读取异常！");
        }
    }

    public static Map<String,String[][]> readExcel(InputStream fis){
        Map<String,String[][]> result = new HashMap<>();
        try {
            //获取整个excel
            XSSFWorkbook hb =new XSSFWorkbook(fis);
            for (int i = 0; i < hb.getNumberOfSheets(); i++) {
                //获取第一个表单sheet
                XSSFSheet sheet = hb.getSheetAt(i);
                //获取第一行
                int firstrow = sheet.getFirstRowNum();
                //获取最后一行
                int lastrow = sheet.getLastRowNum();

                String[][] cellValues = new String[lastrow+1][sheet.getRow(0).getLastCellNum()];
                System.out.println(sheet.getSheetName());

                //循环行数依次获取列数
                for (int j = firstrow; j < lastrow+1; j++) {
                    //获取哪一行i
                    Row row = sheet.getRow(j);
                    if (row != null) {
                        //获取这一行的第一列
                        int firstcell = row.getFirstCellNum();
                        //获取这一行的最后一列
                        int lastcell = row.getLastCellNum();
                        for (int k = firstcell; k <lastcell; k++) {
                            //获取第j列
                            Cell cell = row.getCell(k,Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
                            if (cell!=null){
                                switch (cell.getCellType()) {
                                    case STRING:
                                        cellValues[j][k] = cell.getStringCellValue();
                                        break;
                                    case NUMERIC:
                                        if (DateUtil.isCellDateFormatted(cell)) {
                                            // 转换为LocalDateTime
                                            LocalDateTime localDateTime = cell.getDateCellValue().toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
                                            // 定义日期格式
                                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern(DEFAULT_DATE_PATTERN);
                                            cellValues[j][k] = localDateTime.format(formatter);
                                        } else {
                                            cellValues[j][k] = String.valueOf(cell.getNumericCellValue());
                                        }
                                        break;
                                    case BOOLEAN:
                                        cellValues[j][k] = String.valueOf(cell.getBooleanCellValue());
                                        break;
                                    default:
                                        cellValues[j][k] = "";
                                }
                            }else {
                                cellValues[j][k] = "";
                            }
                        }
                    }
                }
                result.put(sheet.getSheetName(),cellValues);
            }
            fis.close();
        } catch (Exception e){
            throw new RuntimeException("Excel解析错误");
        }
        return result;
    }
}
